﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spRestoreChangeUsersLogin]
#-- Purpose:		Remaps database user accounts after a restore
#--	Last Update:	06/01/2012
#--					For a complete history - please review comments in SVN
#-- Called By:		[dbo].[spRestoreDatabase_BackupSetID]
#--					[dbo].[spRestoreDatabase_Label]
#--					[dbo].[spRestoreDatabase_PointInTime]
#--					Restore Database Utility Executable
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spRestoreChangeUsersLogin]
(
	@database_name		varchar(255)
)
WITH EXECUTE AS OWNER
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE	@sError varchar(1000), @has_rights bit

--- Declare Local Variables
DECLARE @sSQL nvarchar(MAX)

SELECT	@sSQL = '
USE [' + @database_name + '];

--- Declare Local Variables
DECLARE		@sCommand nvarchar(4000)
DECLARE		@users TABLE (UserName sysname, UserSID uniqueidentifier)
DECLARE		@progname sysname

--- Define the name of this program (for logs)
SELECT		@progname = ''spRestoreChangeUsersLogin''

--- Get a list of user accounts that need mapping
SELECT	@sCommand = ''EXEC sp_change_users_login ''''Report'''';''
INSERT INTO @users
	EXEC	(@sCommand)
SELECT	@sCommand = NULL

--- Remap where possible
DECLARE crs CURSOR FOR
	SELECT	''EXEC sp_change_users_login ''''Update_One'''', '''''' + UserName + '''''', '''''' + name + '''''';''
	FROM	@users u
	JOIN	master.dbo.syslogins su ON u.UserName = su.name
OPEN crs
FETCH NEXT FROM crs INTO @sCommand
WHILE @@FETCH_STATUS <> -1
  BEGIN
	IF @sCommand IS NOT NULL
	  BEGIN
		EXEC	(@sCommand)
		EXEC	[' + DB_NAME() + '].dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
	  END

	FETCH NEXT FROM crs INTO @sCommand
  END
CLOSE crs
DEALLOCATE crs

--- Display any remaining user accounts that need mapping
DELETE FROM @users
SELECT	@sCommand = ''EXEC sp_change_users_login ''''Report'''';''
INSERT INTO @users
	EXEC	(@sCommand)

IF EXISTS (SELECT TOP 1 * FROM @users)
	SELECT * FROM @users'
	
EXEC	(@sSQL)

SET NOCOUNT OFF